package DBLayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import DBLayer.DBConnection1;
import DBLayer.IFDBSalesOrder;
import ModelLayer.*;


public class DBSalesOrder implements IFDBSalesOrder{
	private Connection con;
	
	public DBSalesOrder()
	{
		con = DBConnection1.getInstance().getDBcon();
	}
	
//Implements interface methods
	//get all orders
	public ArrayList<SalesOrder> getAllSalesOrders(boolean retrieveAssociation)
	{
		return miscWhere("", retrieveAssociation);
	}
	
	//find all orders for a customer
	public ArrayList<SalesOrder> getCustomerOrders(int customerId, boolean retrieveAssociation)
	{
		return miscWhere("", retrieveAssociation);
	}
	
	//find order by id
	public SalesOrder searchSalesOrder(int orderId, boolean retrieveAssociation)
	{
		return singleWhere("", retrieveAssociation);
	}
	
//private methods
    //michWere is used whenever we want to select more than one employee
	private ArrayList<SalesOrder> miscWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		ArrayList<SalesOrder> list = new ArrayList<>();
		
		String query=buildQuery(wClause);
		
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			
			while(results.next())
			{
				SalesOrder order = new SalesOrder();
				order = buildSO(results);
				list.add(order);
			}
			stmt.close();
			if(retrieveAssociation)
			{
				for(SalesOrder order : list)
				{
					int customerId = order.getCustomer().getCustomerID();
					//.......
				}
			}
		} catch (Exception e) {
			System.out.println("Query exception - select: "+e);
			e.printStackTrace();
		}
		
		return list;
	}
	
	//Singelwhere is used when we only select one employee 	
	private SalesOrder singleWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		SalesOrder order = new SalesOrder();
		
		String query = buildQuery(wClause);
		System.out.println(query);
		
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			
			if(results.next())
			{
				order=buildSO(results);
				stmt.close();
				if (retrieveAssociation) {
					int customerId = order.getCustomer().getCustomerID();
					//Customer customer = singleWhere("customerId="+customerId, false);
					//order.setCustomer(customer);
					//...
				}
			}
		} catch (Exception e) {
			System.out.println("Query exception: "+e);
		}
		
		return order;
	}
	
	//method to build the query
	private String buildQuery(String wClause)
	{
		String query = "SELECT orderId, orderDate, deliveryStatus, deliveryDate, customerId, invoiceNo FROM SalesOrder";
		if (wClause.length()>0) {
			query = query + "WHERE " + wClause;
		}
		
		return query;
	}
	
	//method to build the object
	private SalesOrder buildSO(ResultSet results)
	{
		SalesOrder order = new SalesOrder();
		try {
			order.setOrderId(results.getInt("orderId"));
			order.setOrderDate(results.getString("orderDate"));
			order.setDeliveryDate(results.getString("deliveryDate"));
			order.setDeliveryStatus(results.getString("deliveryStatus"));
			order.setCustomer(new Customer(results.getInt("customerId")));
			order.setInvoice(new Invoice(results.getInt("invoiceNo")));
		} catch (Exception e) {
			System.out.println("error in building the SalesOrder object");
		}
		return order;
	}
}
